

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" href="/img/favicon.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="author" content="Cheney">
  <meta name="keywords" content="Coding">
  
    <meta name="description" content="本程序模拟一个外卖平台系统，该系统由三端构成即用户+商家+配送员。实现的功能：- 用户：登录&#x2F;注册功能，修改用户信息功能，充值账户余额功能，获取正在营业的店铺菜单功能，点外卖提交订单功能，查询订单功能。- 商家：登录&#x2F;注册功能，一键上&#x2F;下班功能 ，修改商家信息功能，营业额提现功能，商品管理模块包括：查询商品详情、新建商品、修改原有商品、删除商品功能。接单窗口显示已接单未出餐商品，标记餐品为我已出餐">
<meta property="og:type" content="article">
<meta property="og:title" content="外卖数据库管理系统">
<meta property="og:url" content="https://cheney822.gitee.io/2021/06/06/%E6%95%B0%E6%8D%AE%E5%BA%93__%E5%A4%96%E5%8D%96%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86%E7%B3%BB%E7%BB%9F/index.html">
<meta property="og:site_name" content="Cheney blog">
<meta property="og:description" content="本程序模拟一个外卖平台系统，该系统由三端构成即用户+商家+配送员。实现的功能：- 用户：登录&#x2F;注册功能，修改用户信息功能，充值账户余额功能，获取正在营业的店铺菜单功能，点外卖提交订单功能，查询订单功能。- 商家：登录&#x2F;注册功能，一键上&#x2F;下班功能 ，修改商家信息功能，营业额提现功能，商品管理模块包括：查询商品详情、新建商品、修改原有商品、删除商品功能。接单窗口显示已接单未出餐商品，标记餐品为我已出餐">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210702133634282-20220405210941039.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701233306635-20220405210941137.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701234346235-20220405210941175.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701234411714-20220405210941212.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701233708815-20220405210941250.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701233951475-20220405210941287.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701234317557-20220405210941323.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701234248872-20220405210941359.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701235709700-20220405210941407.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210701235631467-20220405210941467.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210705134805130-20220405210941508.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/2021070515070081-20220405210941550.png">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/992a49478a0a42869b872971a13fc151-20220405210931765-20220405210941702.gif">
<meta property="article:published_time" content="2021-06-06T00:22:00.000Z">
<meta property="article:modified_time" content="2022-04-05T13:14:29.343Z">
<meta property="article:author" content="Cheney">
<meta property="article:tag" content="Python">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="MySql">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://imgbed.cheney.cc/picgo/20210702133634282-20220405210941039.png">
  
  
  <title>外卖数据库管理系统 - Cheney blog</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4/github-markdown.min.css" />
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hint.css@2/hint.min.css" />

  
    
    
      
      <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@10/styles/github-gist.min.css" />
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.css" />
  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_lbnruvf0jn.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"cheney822.gitee.io","root":"/","version":"1.8.14","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"right","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":1},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname","ignore_local":false}},"search_path":"/local-search.xml"};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.4.1"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>Cheney Blog</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('https://imgbed.cheney.cc/Blog_config/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="外卖数据库管理系统">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2021-06-06 08:22" pubdate>
        2021年6月6日 早上
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      11k 字
    </span>
  

  
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      55 分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">外卖数据库管理系统</h1>
            
            <div class="markdown-body">
              <blockquote>
<p>环境  MySQL Community Server 8.0.25 、Python 3.8、Tkinter</p>
</blockquote>
<hr>
<h1 id="需求分析"><a href="#需求分析" class="headerlink" title="需求分析"></a>需求分析</h1><p><strong>本程序模拟一个外卖平台系统，该系统由三端构成即用户+商家+配送员。</strong><br>不同的用户(三类)有不同的ID，姓名可重名。每个用户可以下多个订单，每个订单对应一个配送员，一个订单内可包含多个同一家店铺的商品，每个商家可以同时处理来自多个用户的多个订单。一个配送员可以同时派送多个订单，订单对应的配送员由外卖平台统一分配。<br>三类用户均可以以自己的账号密码登录对应的系统，可以注册新账号</p>
<h2 id="实现的功能："><a href="#实现的功能：" class="headerlink" title="实现的功能："></a>实现的功能：</h2><ul>
<li><p>用户：登录/注册功能，修改用户信息功能，充值账户余额功能，获取正在营业的店铺菜单功能，点外卖提交订单功能，查询订单功能。</p>
</li>
<li><p>商家：登录/注册功能，一键上/下班功能 ，修改商家信息功能，营业额提现功能，商品管理模块包括：查询商品详情、新建商品、修改原有商品、删除商品功能。接单窗口显示已接单未出餐商品，标记餐品为我已出餐功能，查询最近已出餐商品功能。</p>
</li>
<li><p>配送员：登录/注册功能，一键上/下班功能 ，修改个人信息功能，工资提现功能，工作窗口显示派送给自己的所有订单，修改订单状态为已完成（点击我已送达）功能，查看最近派送完成的订单功能。</p>
<img src="https://imgbed.cheney.cc/picgo/20210702133634282-20220405210941039.png" srcset="/img/loading.gif" lazyload/></li>
</ul>
<h1 id="数据库设计"><a href="#数据库设计" class="headerlink" title="数据库设计"></a>数据库设计</h1><h2 id="逻辑结构设计"><a href="#逻辑结构设计" class="headerlink" title="逻辑结构设计"></a>逻辑结构设计</h2><ul>
<li>商家：店名，商家号，登录密码，地址，电话，营业额，状态，</li>
<li>商品：编号，商品名，价格，商家号，库存</li>
<li>骑手：编号，登录密码，姓名，性别，电话，工资，状态，</li>
<li>顾客：ID，登录密码，姓名，性别，地址， 电话，余额。</li>
<li>订单：订单号，骑手号，顾客ID，状态，备注，配送费，金额，发起时间</li>
<li>订单详情：订单号，商品号，数量</li>
</ul>
<h2 id="关系属性"><a href="#关系属性" class="headerlink" title="关系属性"></a>关系属性</h2><ul>
<li>Store（Sno，Spass，Sname，Saddr，Stel，Smoney，Sstate）</li>
<li>Goods（Gno，Sno，Gname，Gprice，Gstock）</li>
<li>Customer（Cno，Cpass，Cname，Csex，Caddr，Ctel，Cmoney）</li>
<li>Deliverer（Dno，Dpass，Dname，Dsex，Dtel，Dmoney，Dstate）</li>
<li>Order（Ono，Dno，Cno，Ostate，Otip，ODelfee，Omoney，Obtime）</li>
<li>Purchase（Ono，Gno，Pamount）</li>
</ul>
<h2 id="E-R图"><a href="#E-R图" class="headerlink" title="E-R图"></a>E-R图</h2><img src="https://imgbed.cheney.cc/picgo/20210701233306635-20220405210941137.png" srcset="/img/loading.gif" lazyload/>

<h2 id="数据表"><a href="#数据表" class="headerlink" title="数据表"></a>数据表</h2><h3 id="商店表（store）"><a href="#商店表（store）" class="headerlink" title="商店表（store）"></a>商店表（store）</h3><ul>
<li>主码：Sno</li>
<li>自定义完整性：<br>CHECK (Sstate IN (‘工作’,’休息’)),<br>CHECK (Smoney &gt;= 0)<br><img src="https://imgbed.cheney.cc/picgo/20210701234346235-20220405210941175.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<h3 id="商品表（goods）"><a href="#商品表（goods）" class="headerlink" title="商品表（goods）"></a>商品表（goods）</h3><ul>
<li>主码：Gno</li>
<li>外码：FOREIGN KEY (Sno) REFERENCES Store(Sno)<br><img src="https://imgbed.cheney.cc/picgo/20210701234411714-20220405210941212.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<h3 id="顾客表（Customer）"><a href="#顾客表（Customer）" class="headerlink" title="顾客表（Customer）"></a>顾客表（Customer）</h3><ul>
<li>主码：Cno</li>
<li>自定义完整性：<br>CHECK (Csex IN (‘M’,’F’)),<br>CHECK (Cmoney &gt;= 0)<br><img src="https://imgbed.cheney.cc/picgo/20210701233708815-20220405210941250.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<h3 id="派送员表（deliverer）"><a href="#派送员表（deliverer）" class="headerlink" title="派送员表（deliverer）"></a>派送员表（deliverer）</h3><ul>
<li>主码：Dno</li>
<li>自定义完整性：<br>CHECK (Dsex IN (‘M’,’F’)),<br>CHECK (Dstate IN (‘工作’,’休息’)),<br>CHECK (Dmoney &gt;= 0)<br><img src="https://imgbed.cheney.cc/picgo/20210701233951475-20220405210941287.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<h3 id="订单表（orderr）"><a href="#订单表（orderr）" class="headerlink" title="订单表（orderr）"></a>订单表（orderr）</h3><ul>
<li>主码：Ono</li>
<li>外码：<br>FOREIGN KEY (Dno) REFERENCES Deliverer(Dno),<br>FOREIGN KEY (Cno) REFERENCES Customer(Cno) ,<br>FOREIGN KEY (Sno) REFERENCES Store(Sno) ,</li>
<li>自定义完整性：<br>CHECK (Omoney &gt;= 0),<br>CHECK (ODelfee &gt;= 0),<br>CHECK (Ostate IN(‘正在出餐’,’正在配送’,’订单完成’))<br><img src="https://imgbed.cheney.cc/picgo/20210701234317557-20220405210941323.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<h3 id="订单详情表（purchase）"><a href="#订单详情表（purchase）" class="headerlink" title="订单详情表（purchase）"></a>订单详情表（purchase）</h3><ul>
<li>主码：PRIMARY KEY (Ono, Gno ),</li>
<li>外码：FOREIGN KEY (Ono) REFERENCES Orderr(Ono) ,<br>FOREIGN KEY (Gno) REFERENCES Goods(Gno) ,<br><img src="https://imgbed.cheney.cc/picgo/20210701234248872-20220405210941359.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<h2 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h2><h3 id="用户点单视图"><a href="#用户点单视图" class="headerlink" title="用户点单视图"></a>用户点单视图</h3><p>用户点单时可以看见所有商家的所有菜品。用户需要看到的是商店名而不是商店编号，商品名而不是商品编号。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><div class="code-wrapper"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></div></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> view_Cus_buy <span class="hljs-keyword">AS</span> <br><span class="hljs-keyword">SELECT</span> Sname <span class="hljs-string">&#x27;店名&#x27;</span>,Gname <span class="hljs-string">&#x27;商品&#x27;</span>,Gprice <span class="hljs-string">&#x27;价格&#x27;</span><br><span class="hljs-keyword">FROM</span> goods,store<br><span class="hljs-keyword">WHERE</span> goods.Sno <span class="hljs-operator">=</span> store.Sno<br><span class="hljs-keyword">AND</span> store.Sstate <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;工作&#x27;</span><br><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> store.Sno ;<br></code></pre></td></tr></table></figure>

<h3 id="用户订单视图"><a href="#用户订单视图" class="headerlink" title="用户订单视图"></a>用户订单视图</h3><p>用户可以看到订单的信息。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> view_Cus_look <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span><br>orderr.Ono,<br>store.Sname,<br>orderr.Omoney,<br>orderr.Ostate,<br>orderr.Obtime,<br>orderr.Cno<br><span class="hljs-keyword">FROM</span> orderr,store <br><span class="hljs-keyword">WHERE</span> store.Sno <span class="hljs-operator">=</span> Orderr.Sno<br><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> orderr.Obtime <span class="hljs-keyword">DESC</span> ;<br></code></pre></td></tr></table></figure>

<h3 id="密码视图"><a href="#密码视图" class="headerlink" title="密码视图"></a>密码视图</h3><p>将三类用户的密码整合在一起，并根据来源添加类型字段。方便验证密码。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> Password ID,<span class="hljs-string">&#x27;密码&#x27;</span>,<span class="hljs-string">&#x27;类型&#x27;</span><span class="hljs-keyword">AS</span> <br><span class="hljs-keyword">SELECT</span> Cno ,Cpass ,<span class="hljs-string">&#x27;用户&#x27;</span> <br><span class="hljs-keyword">FROM</span> Customer<br><span class="hljs-keyword">UNION</span><br><span class="hljs-keyword">SELECT</span> Sno,Spass,<span class="hljs-string">&#x27;商家&#x27;</span><br><span class="hljs-keyword">FROM</span> Store<br><span class="hljs-keyword">UNION</span><br><span class="hljs-keyword">SELECT</span> Dno,Dpass,<span class="hljs-string">&#x27;配送员&#x27;</span><br><span class="hljs-keyword">FROM</span> Deliverer ;<br></code></pre></td></tr></table></figure>

<h3 id="配送员视图"><a href="#配送员视图" class="headerlink" title="配送员视图"></a>配送员视图</h3><p>骑手无法查看订单所有信息，需要快速查看地址电话等，故需要单独设计视图。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> view_del_unfinish <span class="hljs-keyword">AS</span> <br><span class="hljs-keyword">SELECT</span> store.Sname,customer.Caddress,customer.Ctel,orderr.ODelfee,orderr.Ono<br><span class="hljs-keyword">FROM</span> store,customer,orderr<br><span class="hljs-keyword">WHERE</span> orderr.Ostate <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;正在配送&#x27;</span><br><span class="hljs-keyword">AND</span> orderr.Sno<span class="hljs-operator">=</span>store.Sno<br><span class="hljs-keyword">AND</span> orderr.Cno<span class="hljs-operator">=</span>customer.Cno<br><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> orderr.Obtime <span class="hljs-keyword">DESC</span> ;<br></code></pre></td></tr></table></figure>

<h3 id="商家端订单视图"><a href="#商家端订单视图" class="headerlink" title="商家端订单视图"></a>商家端订单视图</h3><p>商家看到的待出餐的订单信息。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> view_store_unfinish <span class="hljs-keyword">AS</span> <br><span class="hljs-keyword">SELECT</span> orderr.Ono,customer.Cname,customer.Ctel,orderr.Omoney<br><span class="hljs-keyword">FROM</span> store,customer,orderr<br><span class="hljs-keyword">WHERE</span> orderr.Ostate <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;正在出餐&#x27;</span><br><span class="hljs-keyword">AND</span> orderr.Sno<span class="hljs-operator">=</span>store.Sno<br><span class="hljs-keyword">AND</span> orderr.Cno<span class="hljs-operator">=</span>customer.Cno<br><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> orderr.Obtime <span class="hljs-keyword">DESC</span> ;<br></code></pre></td></tr></table></figure>

<h2 id="存储过程"><a href="#存储过程" class="headerlink" title="存储过程"></a>存储过程</h2><h3 id="利用存储过程实现对金融的操作-扣款-提现-充值"><a href="#利用存储过程实现对金融的操作-扣款-提现-充值" class="headerlink" title="利用存储过程实现对金融的操作(扣款/提现/充值)"></a>利用存储过程实现对金融的操作(扣款/提现/充值)</h3><p>顾客的充值与扣费函数+配送员获得工作与提现函数+商家获得订单利润与提现函数。<br>使用存储过程的好处：可以使计算过程在服务器完成，减轻本地负担，对敏感信息的操作与客户端分离，增强安全性。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> alter_del_money(<span class="hljs-keyword">IN</span> user_id <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">20</span>),<span class="hljs-keyword">IN</span> amount <span class="hljs-type">INT</span>)<br><span class="hljs-keyword">BEGIN</span> <br><span class="hljs-keyword">update</span> deliverer <span class="hljs-keyword">set</span> Dmoney<span class="hljs-operator">=</span>amount <span class="hljs-keyword">WHERE</span> Dno<span class="hljs-operator">=</span>user_id;<br><span class="hljs-keyword">END</span> $<br>DELIMITER ;<br></code></pre></td></tr></table></figure>

<h2 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h2><h3 id="插入具体商品-Purchase表-时更新订单-Orderr表-信息"><a href="#插入具体商品-Purchase表-时更新订单-Orderr表-信息" class="headerlink" title="插入具体商品(Purchase表)时更新订单(Orderr表)信息"></a>插入具体商品(Purchase表)时更新订单(Orderr表)信息</h3><p>这里在插入一个商品后自动在对应的订单增加一个数量（对应配送费），修改对应的总金额。<br>使用触发器的好处：自动完成修改，防止遗漏，且操作在服务器，与客户端分离。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">BEGIN</span><br>	<span class="hljs-keyword">DECLARE</span> a <span class="hljs-type">INT</span>;<br>	<span class="hljs-keyword">DECLARE</span> b <span class="hljs-type">INT</span>;<br>	<span class="hljs-keyword">DECLARE</span> c <span class="hljs-type">INT</span>;<br>	<br>	<span class="hljs-keyword">SET</span> a <span class="hljs-operator">=</span> ( <span class="hljs-keyword">SELECT</span> orderr.ODelfee <span class="hljs-keyword">FROM</span> orderr <span class="hljs-keyword">WHERE</span> orderr.Ono <span class="hljs-operator">=</span> new.Ono );<br>	<span class="hljs-keyword">SET</span> b <span class="hljs-operator">=</span> ( <span class="hljs-keyword">SELECT</span> orderr.Omoney <span class="hljs-keyword">FROM</span> orderr <span class="hljs-keyword">WHERE</span> orderr.Ono <span class="hljs-operator">=</span> new.Ono );<br>	<span class="hljs-keyword">SET</span> c <span class="hljs-operator">=</span> ( <span class="hljs-keyword">SELECT</span> goods.Gprice <span class="hljs-keyword">FROM</span> goods <span class="hljs-keyword">WHERE</span> goods.Gno <span class="hljs-operator">=</span> new.Gno );<br>	<span class="hljs-keyword">UPDATE</span> orderr <br>	<span class="hljs-keyword">SET</span> orderr.ODelfee <span class="hljs-operator">=</span> a <span class="hljs-operator">+</span> <span class="hljs-number">1</span>,<br>		orderr.Omoney <span class="hljs-operator">=</span> b <span class="hljs-operator">+</span> c <br>	<span class="hljs-keyword">WHERE</span><br>		orderr.Ono <span class="hljs-operator">=</span> new.Ono;<br><span class="hljs-keyword">END</span><br></code></pre></td></tr></table></figure>


<hr>
<h1 id="UI设计"><a href="#UI设计" class="headerlink" title="UI设计"></a>UI设计</h1><h2 id="主界面"><a href="#主界面" class="headerlink" title="主界面"></a>主界面</h2><p>三个程序在界面上大体相同，我把他们的主界面分为几个几个区域：1.选择列表区 2.订单信息区 3.功能区</p>
<ul>
<li>选择列表区:显示部分主要是由标题加表格和勾选框以及滑动条组成，操作有全选/取消全选、提交操作、刷新列表。</li>
<li>订单信息区：这个区域是只读的，只有刷新列表一个功能。</li>
<li>功能区：这个区域实时的显示用户信息并且有若干功能按钮，点击相应的按钮就进入相应的功能模块。<br><img src="https://imgbed.cheney.cc/picgo/20210701235709700-20220405210941407.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></li>
</ul>
<p>主界面是用<code>tkinter.TK（）</code>创建出来的，起到 ‘根界面’ 的作用即其它所有界面都是基于他来做的。创建主界面之后要用<code>mainloop</code>将其显示出来。由于实际操作中需要先登录再进入主界面所以先把主界面用<code>.withdrew()</code>方法隐藏起来，成功登录之后再用<code>main_window.deiconify()</code>将其显示出来。</p>
<h2 id="登录界面"><a href="#登录界面" class="headerlink" title="登录界面"></a>登录界面</h2><p>打开程序最先跳出的就是登陆界面，在该界面输入用户密码后点击登录，如果验证正确则进入主界面，如果验证失败则跳出警告框提示，用户名密码信息均来自数据库。如果账户类型和程序不匹配也无法进入程序。<br>点击注册按钮会跳出注册界面，输入信息后点击确认注册，则会在对应的用户类型的数据表中插入一条记录，注册成功后就可以用该账户登录系统。<br><img src="https://imgbed.cheney.cc/picgo/20210701235631467-20220405210941467.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>
<h2 id="子窗口"><a href="#子窗口" class="headerlink" title="子窗口"></a>子窗口</h2><p>点击一些功能按钮(如注册、修改信息、商品管理等)时会跳出一个子窗口在主界面上层以完成相应的模块的功能。<br>这里是用<code>tk.Toplevel()</code>实现的，比如</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs python">window_sign_up = tk.Toplevel(loggin_window) <br></code></pre></td></tr></table></figure>


<p>就在loggin_window界面的基础上创建了window_sign_up子界面。tk.Toplevel()和tk.TK()的功能几乎完全一样，不同的是tk.TK()是根界面不建立在其它界面之上。<br>这里采用模块化的设计方式，即一个子界面的所有内容都包装在一个函数(Python允许函数嵌套)或者一个类当中。<br>我这里大部分都是用函数嵌套来完成的，首先在函数内部定义子功能对应的子函数，然后设计对应子界面上的控件，并在控件的响应函数中调用上述定义的子函数。</p>
<h2 id="消息窗口"><a href="#消息窗口" class="headerlink" title="消息窗口"></a>消息窗口</h2><p><code>messagebox</code>用于弹出消息窗口，通常用于显示提示消息，例如登录成功、登录失败</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">tk.messagebox.showerror(message<span class="hljs-operator">=</span><span class="hljs-string">&#x27;密码错误&#x27;</span>)<br>tk.messagebox.showinfo(title<span class="hljs-operator">=</span><span class="hljs-string">&#x27;welcome&#x27;</span>,message<span class="hljs-operator">=</span><span class="hljs-string">&#x27;登陆成功&#x27;</span>)<br></code></pre></td></tr></table></figure>

<p>以上是两类不同的messagebox，一个用来显示常规信息，一个用来提示错误信息。<br>每个消息窗口都可以用title=‘’来指定信息窗口的标签，用message=‘’来指定消息窗口的内容。</p>
<h2 id="标签"><a href="#标签" class="headerlink" title="标签"></a>标签</h2><p><code>label</code>一般用来显示静态的文本或者图像，比如</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">tk.Label(main_window, text<span class="hljs-operator">=</span><span class="hljs-string">&#x27;用户名:&#x27;</span>).place(x<span class="hljs-operator">=</span>left_index, y<span class="hljs-operator">=</span><span class="hljs-number">15</span>)<br>tk.Label(main_window, text<span class="hljs-operator">=</span><span class="hljs-string">&#x27;余  额:&#x27;</span>).place(x<span class="hljs-operator">=</span>left_index, y<span class="hljs-operator">=</span><span class="hljs-number">55</span>)<br>tk.Label(main_window, text<span class="hljs-operator">=</span><span class="hljs-string">&#x27;已选:&#x27;</span>).place(x<span class="hljs-operator">=</span><span class="hljs-number">40</span>, y<span class="hljs-operator">=</span><span class="hljs-number">480</span>)<br>tk.Label(main_window, text<span class="hljs-operator">=</span><span class="hljs-string">&#x27;最近购买的订单：&#x27;</span>).place(x<span class="hljs-operator">=</span><span class="hljs-number">440</span>, y<span class="hljs-operator">=</span><span class="hljs-number">180</span>)<br></code></pre></td></tr></table></figure>

<p>表示在main_window,这个界面上设置标签，放置位置在(x,y)处，内容是text。</p>
<p><code>label</code>还可以用来显示动态的内容，根据程序的进行不断改变内容。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">total_wallet_show <span class="hljs-operator">=</span> tk.StringVar()  # 用于展示余额<br>tk.Label(main_window, textvariable<span class="hljs-operator">=</span>total_wallet_show).place(x<span class="hljs-operator">=</span>left_index <span class="hljs-operator">+</span> <span class="hljs-number">40</span>, y<span class="hljs-operator">=</span><span class="hljs-number">55</span>)<br><br>total_wallet_show.set(rest_money)<br></code></pre></td></tr></table></figure>

<p>以上建立了一个标签，显示内容为<code>total_wallet_show</code>，其是<code>tk.StringVar()</code>类型的变量，然后在程序需要刷新这个内容的时候调用<code>total_wallet_show.set（)</code>将其标签上的内容刷新。</p>
<h2 id="按钮"><a href="#按钮" class="headerlink" title="按钮"></a>按钮</h2><p>按钮<code>button</code>用来实现用户对某个功能的选择，button是可以点击的，点击相应的button就会跳转到对应的处理函数。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">bt_charge <span class="hljs-operator">=</span> tk.Button(main_window, text<span class="hljs-operator">=</span><span class="hljs-string">&#x27;充值余额&#x27;</span>, height<span class="hljs-operator">=</span><span class="hljs-number">1</span>, width<span class="hljs-operator">=</span><span class="hljs-number">16</span>, command<span class="hljs-operator">=</span>charge_money)<br>bt_charge.place(x<span class="hljs-operator">=</span>left_index <span class="hljs-operator">-</span> <span class="hljs-number">140</span>, y<span class="hljs-operator">=</span><span class="hljs-number">50</span>)<br>bt_user_info <span class="hljs-operator">=</span> tk.Button(main_window, text<span class="hljs-operator">=</span><span class="hljs-string">&#x27;查询修改个人信息&#x27;</span>, height<span class="hljs-operator">=</span><span class="hljs-number">1</span>, width<span class="hljs-operator">=</span><span class="hljs-number">16</span>, command<span class="hljs-operator">=</span>alter_info)<br>bt_user_info.place(x<span class="hljs-operator">=</span>left_index <span class="hljs-operator">-</span> <span class="hljs-number">140</span>, y<span class="hljs-operator">=</span><span class="hljs-number">10</span>)<br></code></pre></td></tr></table></figure>

<p>比如这里设置了bt_charge和bt_user_info两个按钮，按钮放置在main_window的(x,y)位置，按钮上的显示信息是test，按钮大小是height X width，点击按钮会跳转到command指定的函数中去。</p>
<h2 id="列表"><a href="#列表" class="headerlink" title="列表"></a>列表</h2><p>本程序中的用户点单列表、订单列表、商品管理里面的商品详情等都是用<code>Treeview</code>来做的，比如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs sql">columns <span class="hljs-operator">=</span> [<span class="hljs-string">&#x27;订单号&#x27;</span>, <span class="hljs-string">&#x27;商家名&#x27;</span>, <span class="hljs-string">&#x27;金额&#x27;</span>, <span class="hljs-string">&#x27;状态&#x27;</span>, <span class="hljs-string">&#x27;日期&#x27;</span>]<br>width_ord <span class="hljs-operator">=</span> [<span class="hljs-number">45</span>, <span class="hljs-number">70</span>, <span class="hljs-number">50</span>, <span class="hljs-number">70</span>, <span class="hljs-number">110</span>]<br>order_list_table <span class="hljs-operator">=</span> Treeview( master<span class="hljs-operator">=</span>main_window, height<span class="hljs-operator">=</span><span class="hljs-number">10</span>,  columns<span class="hljs-operator">=</span>columns, <span class="hljs-keyword">show</span><span class="hljs-operator">=</span><span class="hljs-string">&#x27;headings&#x27;</span>)<br><br>order_list_table.place(x<span class="hljs-operator">=</span><span class="hljs-number">440</span>, y<span class="hljs-operator">=</span><span class="hljs-number">200</span>)<br><span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> columns:<br>    order_list_table.heading(i, text<span class="hljs-operator">=</span>i)  # 定义表头<br>t <span class="hljs-operator">=</span> <span class="hljs-number">0</span><br><span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> columns:<br>    order_list_table.column(i, width<span class="hljs-operator">=</span>width_ord[t], minwidth<span class="hljs-operator">=</span><span class="hljs-number">40</span>, anchor<span class="hljs-operator">=</span>S, )  # 定义列<br>    t <span class="hljs-operator">+</span><span class="hljs-operator">=</span> <span class="hljs-number">1</span><br></code></pre></td></tr></table></figure>

<p>这里定义了一个Tree view表格<code>order_list_table</code>表格共五列，分别对应columns中的元素。<code>width_ord</code>的每一项分别是每列的宽度，其父容器是main_window，高度是10行，展示方式是’headings’即展示首行标题<br> 插入待展示的数据的方式：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">for</span> <span class="hljs-type">row</span> <span class="hljs-keyword">in</span> flu_list:  # 格式：店名、商品、价格<br>       i <span class="hljs-operator">+</span><span class="hljs-operator">=</span> <span class="hljs-number">1</span><br>       order_list_table.insert(<span class="hljs-string">&#x27;&#x27;</span>, <span class="hljs-string">&#x27;end&#x27;</span>, <span class="hljs-keyword">values</span><span class="hljs-operator">=</span><span class="hljs-type">row</span>)<br></code></pre></td></tr></table></figure>

<p>这里的<code>flu_list</code>是一个从数据库中获取的二位列表<br>通过这种方式就把数据库中的数据展示在表格中了。</p>
<h2 id="勾选框"><a href="#勾选框" class="headerlink" title="勾选框"></a>勾选框</h2><p>用户点单，商家和配送员完成订单都需要勾选列表中的内容，这里利用 <code>check button</code>实现，在treeview的每一行前面都设置一个checkbutton，由于treeview和checkbutton都是有编号的，所以可以将每一个勾选框的勾选状态和列表元素的选择联系起来。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs sql">ck_button[<span class="hljs-string">&#x27;command&#x27;</span>] <span class="hljs-operator">=</span> lambda item<span class="hljs-operator">=</span>tv_item: self.select_button(item)<br><br>def select_button(self, item):<br>    a <span class="hljs-operator">=</span> eval(&quot;0x&quot; <span class="hljs-operator">+</span> str(item[<span class="hljs-number">1</span>:])) <span class="hljs-operator">%</span> len(cur_menu)<br>    if a <span class="hljs-keyword">in</span> order_list:<br>        order_list.remove(a)<br>    <span class="hljs-keyword">else</span>:<br>        order_list.add(a)<br></code></pre></td></tr></table></figure>

<p>这里的order_list 集合内存的就是勾选的行对应在菜单内的编号，即勾选了的行的编号都在order_list内。</p>
<hr>
<h1 id="数据库相关算法设计"><a href="#数据库相关算法设计" class="headerlink" title="数据库相关算法设计"></a>数据库相关算法设计</h1><h2 id="连接数据库"><a href="#连接数据库" class="headerlink" title="连接数据库"></a>连接数据库</h2><p>这里使用的是pymysql连接到本地的mysql数据库。</p>
<p>连接之后利用cursor方法创建普通的游标对象。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs python"><span class="hljs-keyword">import</span> pymysql<br><br><span class="hljs-comment"># 连接数据库</span><br>db = pymysql.connect(host=<span class="hljs-string">&#x27;127.0.0.1&#x27;</span>, user=<span class="hljs-string">&quot;root&quot;</span>, passwd=<span class="hljs-string">&quot;cc000822&quot;</span>, db=<span class="hljs-string">&quot;restaurant&quot;</span>)<br><span class="hljs-comment"># 使用cursor()方法获取操作游标</span><br>cursor = db.cursor()<br></code></pre></td></tr></table></figure>

<h2 id="从数据库获取数据（查）"><a href="#从数据库获取数据（查）" class="headerlink" title="从数据库获取数据（查）"></a>从数据库获取数据（查）</h2><p>这里我自己简单的封装了一个函数，用来做SELECT查询操作。该函数有两个参数，第一个是必选的参数用来指定选择的范围，第二个参数是可选参数用来指定选择条件等。返回值有两个列表类型的量，分别是结果的标题和结果本身(结果是二维列表)，内部设有错误捕获语句，执行出错时会打印出出错的SQL语句，方便调试。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><code class="hljs sql">def <span class="hljs-keyword">select</span>(<span class="hljs-keyword">table</span>, par<span class="hljs-operator">=</span>&quot;&quot;):<br>    # <span class="hljs-keyword">SQL</span> 查询语句<br>    data_dict <span class="hljs-operator">=</span> []<br>    results <span class="hljs-operator">=</span> []<br>    <span class="hljs-keyword">sql</span> <span class="hljs-operator">=</span> &quot;SELECT * FROM %s %s;&quot; <span class="hljs-operator">%</span> \<br>          (<span class="hljs-keyword">table</span>, par)<br>    try:<br>        # 执行<span class="hljs-keyword">SQL</span>语句<br>        cursor.execute(<span class="hljs-keyword">sql</span>)<br>        # 获取所有记录列表<br>        results <span class="hljs-operator">=</span> cursor.fetchall()<br><br>        data_dict <span class="hljs-operator">=</span> []<br>        # 打印出标题<br>        <span class="hljs-keyword">for</span> field <span class="hljs-keyword">in</span> cursor.description:<br>            data_dict.append(field[<span class="hljs-number">0</span>])<br>    <span class="hljs-keyword">except</span>:<br>        print(<span class="hljs-keyword">sql</span> <span class="hljs-operator">+</span> &quot;_失败&quot;)<br>        print(&quot;Error: unable to fetch data&quot;)<br><br>    <span class="hljs-keyword">return</span> data_dict, results<br><br></code></pre></td></tr></table></figure>

<h2 id="插入到数据库（增）"><a href="#插入到数据库（增）" class="headerlink" title="插入到数据库（增）"></a>插入到数据库（增）</h2><p>另一个封装的是插入INSERT函数，这个函数也很简单，两个参数分别是要插入的表(或者表中具体的列)和要插入的数据(VALUES后面的内容包括括号)。该函数没有返回值，若出错则会在错误捕获语句输出该语句，并回滚。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs sql">def <span class="hljs-keyword">Insert</span>(<span class="hljs-keyword">table</span>, par):<br>    <span class="hljs-keyword">sql</span> <span class="hljs-operator">=</span> &quot;INSERT INTO %s VALUES %s;&quot; <span class="hljs-operator">%</span> \<br>          (<span class="hljs-keyword">table</span>, par)<br>    try:<br>        # 执行<span class="hljs-keyword">SQL</span>语句<br>        cursor.execute(<span class="hljs-keyword">sql</span>)<br>        db.commit()<br>        print(<span class="hljs-keyword">sql</span> <span class="hljs-operator">+</span> &quot;成功&quot;)<br>    <span class="hljs-keyword">except</span>:<br>        db.rollback()<br>        print(<span class="hljs-keyword">sql</span> <span class="hljs-operator">+</span> &quot;_失败&quot;)<br>        print(&quot;Error: file to insert&quot;)<br></code></pre></td></tr></table></figure>

<h2 id="修改数据（改）"><a href="#修改数据（改）" class="headerlink" title="修改数据（改）"></a>修改数据（改）</h2><p>修改数据功能在本程序中没有统一的格式，故没有封装成函数的形式。这里就拿修改商家个人信息举例(其余的大同小异)。<br>由于可修改的信息不止一个，每个也都可以选择是否修改(若要修改就在相应位置填值反之不填)，而UPDATE语句的SET之间要用逗号隔开且第一个之前没有逗号。<br>所以要做两个判断 ：</p>
<ol>
<li>判断每个属性是否需要被修改，需要的话就在SQL语句加上相应的部分 </li>
<li>修改当前属性之前是否有属性已经被修改过(当前是否是第一个被修改的属性)，若前面都没有被修改过即当前是第一个被修改的属性则前面不需要加上逗号否则需要加上逗号。</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 将新信息提交到数据库<br>       changed <span class="hljs-operator">=</span> <span class="hljs-number">0</span><br>       sql_alt <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;UPDATE store SET &#x27;</span><br>       if altered_pwd <span class="hljs-operator">!=</span> &quot;&quot;:<br>           sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> &quot; Spass=\&#x27;&quot; <span class="hljs-operator">+</span> altered_pwd <span class="hljs-operator">+</span> &quot;\&#x27;&quot;<br>           changed <span class="hljs-operator">=</span> <span class="hljs-number">1</span><br>       if altered_addr <span class="hljs-operator">!=</span> &quot;&quot;:<br>           if changed <span class="hljs-operator">=</span><span class="hljs-operator">=</span> <span class="hljs-number">1</span>:<br>               sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> <span class="hljs-string">&#x27;,&#x27;</span><br>           sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> &quot; Saddr=\&#x27;&quot; <span class="hljs-operator">+</span> altered_addr <span class="hljs-operator">+</span> &quot;\&#x27;&quot;<br>           changed <span class="hljs-operator">=</span> <span class="hljs-number">1</span><br>       if altered_tel <span class="hljs-operator">!=</span> &quot;&quot;:<br>           if changed <span class="hljs-operator">=</span><span class="hljs-operator">=</span> <span class="hljs-number">1</span>:<br>               sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> <span class="hljs-string">&#x27;,&#x27;</span><br>           sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> &quot; Stel=\&#x27;&quot; <span class="hljs-operator">+</span> altered_tel <span class="hljs-operator">+</span> &quot;\&#x27;&quot;<br>           changed <span class="hljs-operator">=</span> <span class="hljs-number">1</span><br>       if altered_name <span class="hljs-operator">!=</span> &quot;&quot;:<br>           if changed <span class="hljs-operator">=</span><span class="hljs-operator">=</span> <span class="hljs-number">1</span>:<br>               sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> <span class="hljs-string">&#x27;,&#x27;</span><br>           sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> &quot; Sname=\&#x27;&quot; <span class="hljs-operator">+</span> altered_name <span class="hljs-operator">+</span> &quot;\&#x27;&quot;<br>       sql_alt <span class="hljs-operator">+</span><span class="hljs-operator">=</span> &quot;WHERE Sno=\&#x27;&quot; <span class="hljs-operator">+</span> user_id <span class="hljs-operator">+</span> &quot;\&#x27;;&quot;<br>       try:  # 执行<span class="hljs-keyword">SQL</span>语句<br>           cursor.execute(sql_alt)<br>           db.commit()<br>           user_pwd <span class="hljs-operator">=</span> identify_pwd  # 修改本地缓存的密码<br>           tk.messagebox.showinfo(title<span class="hljs-operator">=</span>&quot;通知&quot;, message<span class="hljs-operator">=</span>&quot;修改成功！&quot;)<br>       <span class="hljs-keyword">except</span>:<br>           db.rollback()<br>           print(sql_alt <span class="hljs-operator">+</span> &quot;_失败&quot;)<br></code></pre></td></tr></table></figure>

<hr>
<h1 id="其它算法设计"><a href="#其它算法设计" class="headerlink" title="其它算法设计"></a>其它算法设计</h1><h2 id="涉及金融的修改"><a href="#涉及金融的修改" class="headerlink" title="涉及金融的修改"></a>涉及金融的修改</h2><p>涉及到金融的修改全部不直接用UPDATE的方式，而是调用在服务器端设置的三个存储过程</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql">`alter_cus_money`、`alter_del_money`、`alter_store_money`。<br></code></pre></td></tr></table></figure>

<p>三个存储过程的功能完全相同，不同之处在于面向的对象以及被调用的场景。<br><code>alter_cus_money</code>在用户提交订单并做相应的检查(余额是否足够等)之后扣除本次用户的花费，在用户充值余额并成功验证密码之后增加充值的金额。<br><code>alter_store_money</code>在用户提交订单完成之后，商家界面上的列表出现对应的订单，在商家对订单点击我已出餐之后，商家营业额会增加该订单对应的金额。在商家提现并验证完密码做完安全性检查之后营业额会减去相应的提现金额。<br><code>alter_del_money</code>商家点击我已出餐之后，配送员界面上未配送订单会出现，配送员送完餐后点击我已送达（完成订单）之后配送员的工资会增加该单对应的配送费会。在配送员点击提现按钮，提现并验证完密码做完安全性检查之后营业额会减去相应的提现金额。</p>
<h2 id="顾客提交订单功能"><a href="#顾客提交订单功能" class="headerlink" title="顾客提交订单功能"></a>顾客提交订单功能</h2><p>用户提交订单的时候，程序只知道两个消息 一是提交时刻对应的菜单 二是订单内所有的商品在菜单中的编号，这两个商品分别存在以下全局变量中</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">order_list <span class="hljs-operator">=</span> <span class="hljs-keyword">set</span>()  # 当前购买的<br>cur_menu <span class="hljs-operator">=</span> []  # 当前的菜单<br></code></pre></td></tr></table></figure>

<p>由于每个订单内都有可能包含来自多个商家的多个商品。需要处理的问题是如何正确更新orderr和purchase两个表，如何分配配送员。<br><strong>解决方法：</strong><br>用户下一次订单，系统识别出来自哪些商家，并将其按照商家拆分成不同的订单，每个订单对应一个商家以及一个配送员，每个订单只对应来自一个商家的商品。</p>
<p>实际操作过程中只需要遍历整个列表，并记录当前下的单对应的商店集合，如果当前商品来自的商店不在该集合内则加入进去并在orderr中插入一条记录。然后对于每个商品，其对应的店铺无论是否在集合内(不在的刚才已经新建了，所以最终结果是都在)，均要在purchase表中插入一条记录。</p>
<p>在插入orderr表的时候需要为其分配一个订单号和一个配送员，所以需要事先获取订单表已经存在的最大编号，将新订单号分配为这个最大编号+1，还要获取全部在上班的配送员列表，在列表中随机挑选一个配送员将其信息插入对应的订单中。</p>
<p>订单插入orderrd的时候还没有订单对应的商品，所以订单金额和配送费都是初始值。每在purchase表中插入一条记录时，要在对应的orderr记录配送费加一以及总金额加上商品价格。这里是用触发器来实现的，每次在purchase插入新内容之后自动完成这一任务，不再需要程序干预。</p>
<p>当<code>order_list</code>遍历结束后订单的提交也就结束了。</p>
<h2 id="商家和配送员完成订单功能"><a href="#商家和配送员完成订单功能" class="headerlink" title="商家和配送员完成订单功能"></a>商家和配送员完成订单功能</h2><p>在用户提交一个订单之后，该订单的状态为正在出餐，商家可以在未出餐列表中看到该订单，商家准备完成后，选择相应的订单点击我已出餐，订单状态变为正在配送，配送员在待配送列表中可以看到该订单，配送员配送完成后点击我已送达，订单状态变为订单完成。全程都可以在订单栏看到订单的状态。</p>
<p>商家和配送员的完成订单功能类似，因为程序沿用了客户端的部分代码，命名规则也直接沿用了过来， <code>cur_menu，order_list = set()</code>这两个变量分别代表当前待处理的订单列表，和已选择的订单编号。所以提交处理订单时只需要遍历order_list 列表并找到cur_menu对应编号的订单，修改状态即可。</p>
<h2 id="一键修改工作状态"><a href="#一键修改工作状态" class="headerlink" title="一键修改工作状态"></a>一键修改工作状态</h2><p>商家和配送员都有切换工作状态的功能，按下相应按钮后会在工作/休息之间切换。<br>商家的状态决定了其商品是否能在用户的菜单上显示，用户的菜单只会显示当前在工作状态的商店的菜品，所以处于休息状态的商家无法接到新订单。配送员类似，系统分配新订单给派送员的时候只会分配给在工作状态的配送员。<br>实现方法：<br>在程序中创建一键切换工作状态后，将其command设置为<code>change_work_state()</code>函数，在此函数内会先读取工作状态(全局变量，每次刷新控件时都会更新一次)，然后再根据当前状态生成UPDATE语句提交到数据库执行，并将执行的结果反馈在消息框中。<br><img src="https://imgbed.cheney.cc/picgo/20210705134805130-20220405210941508.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>
<h2 id="商品管理模块"><a href="#商品管理模块" class="headerlink" title="商品管理模块"></a>商品管理模块</h2><p>这个模块有查询商品详情、新建商品、修改原有商品、删除商品四个功能。</p>
<p>点击商品管理后进入该模块，左侧是商品详情区域，展示所有本店铺的商品信息。右上方是输入区，输入对应功能需要的信息，右下方是功能选择区域，点击其中的按钮会读取上方对应的信息并做对应的操作。</p>
<p>新建商品功能需要录入全部的信息并且保证商品ID不重复，价格和库存不为负值。<br>修改商品必须填入ID，其它选择要修改的填写即可。<br>删除商品只需要填入商品ID即可。<br><img src="https://imgbed.cheney.cc/picgo/2021070515070081-20220405210941550.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>
<h2 id="感谢支持"><a href="#感谢支持" class="headerlink" title="感谢支持"></a>感谢支持</h2><p><img src="https://imgbed.cheney.cc/picgo/992a49478a0a42869b872971a13fc151-20220405210931765-20220405210941702.gif" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>

            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/Python/">Python</a>
                    
                      <a class="hover-with-bg" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                      <a class="hover-with-bg" href="/tags/MySql/">MySql</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">
                  
                    本博客所有文章除特别声明外，均采用 <a target="_blank" href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！
                  
                </p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2021/06/21/%E5%9F%BA%E4%BA%8EOpenCv%E7%9A%84%E4%BA%BA%E8%84%B8%E8%AF%86%E5%88%AB%EF%BC%88Python%E5%AE%8C%E6%95%B4%E4%BB%A3%E7%A0%81%EF%BC%89/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">基于OpenCv的人脸识别</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2021/06/04/%E6%95%B0%E6%8D%AE%E5%BA%93__%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AC%94%E8%AE%B0--%E5%9F%BA%E7%A1%80%E9%83%A8%E5%88%86/">
                        <span class="hidden-mobile">数据库笔记--基础部分</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">
  <div class="footer-content">
     <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://cheney822.gitee.io/" target="_blank" rel="nofollow noopener"><span>备用网址</span></a> 
  </div>
  

  
  <!-- 备案信息 -->
  <div class="beian">
    <span>
      <a href="http://beian.miit.gov.cn/" target="_blank" rel="nofollow noopener">
        皖ICP备2022002876号-1
      </a>
    </span>
    
  </div>


  
</footer>


  <!-- SCRIPTS -->
  
  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  <script  src="/js/local-search.js" ></script>



  
    <script  src="/js/img-lazyload.js" ></script>
  



  



  
    <script  src="https://cdn.jsdelivr.net/npm/tocbot@4/dist/tocbot.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4/anchor.min.js" ></script>
  
  
    <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js" ></script>
  






  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
        typing(title);
      
    })(window, document);
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>


</body>
</html>
